raw_data <- read_sheet("https://docs.google.com/spreadsheets/d/1cbmYDfIc2dxHJxBaowojUZZkN31NW4sL_pHw0t9eTTU/edit#gid=477880397",
    range = "Data_extraction_2", skip = 1, col_types = "ccncccccncncccccnncccnccnncncnccnnncncncccccccc")  # Import raw data

processed_data <- filter(raw_data, !PFAS_type == "PFOS_Total")
processed_data <- filter(processed_data, !Species_common == "Fish cake")

write.csv(processed_data, here("data", "Rawdata_updated_2.csv"), row.names = F)

processed_data <- read.csv(here("data", "Rawdata_updated_2.csv")) 

dat <- processed_data %>% mutate(SDc = ifelse(Sc_technical_biological == "biological", Sc, NA), # Calculate the SD of biological replicates for control samples
                                 SDe = ifelse(Se_technical_biological == "biological", Se, NA)) # Calculate the SD of biological replicates for experimental samples

dat <- dat %>% filter(Species_Scientific != "?")

#### Ratio_liquid_fish with "0" for the dry cooking category
dat<-dat %>% mutate(Ratio_liquid_fish_0 = ifelse(Cooking_Category =="No liquid", 0, Ratio_liquid_fish)) # Add a 0 when the cooking category is "No liquid", otherwise keep the same value of Ratio_liquid_fish

# taking out data without species information


# arrange(select(dat, Cooking_Category, Ratio_liquid_fish, Ratio_liquid_fish_0), Cooking_Category) # Checking everything is fine

dat$Temperature_in_Celsius[dat$Temperature_in_Celsius=="?"] <- NA # Replace "?" by missing values
dat$Temperature_in_Celsius <- as.numeric(dat$Temperature_in_Celsius) # Convert integer to numeric

kable(dat, "html") %>% kable_styling("striped", position = "left") %>% scroll_box(width = "100%", height = "500px")